package com.gzhryc.system.dao;

import java.sql.SQLException;
import java.util.List;

import com.gzhryc.jdbc.BaseDao;
import com.gzhryc.jdbc.Conditions;
import com.gzhryc.jdbc.Query;
import com.gzhryc.system.dao.db.Menu;
import com.gzhryc.system.dao.models.MenuEntity;

public class MenuDao extends BaseDao<Menu> {

	public MenuDao(String jdbcKey) {
		super(jdbcKey);
	}

	public MenuEntity getEntityById(Long id) throws SQLException {
		String sql = "select t.*,t1.name as parentName from sys_menu t"
				+ " left join sys_menu t1 on t1.id = t.parent_id where t.id = ?";
		Query query = new Query(sql);
		query.getParams().setObject(id);
		return this.jdbcHelper.get(query, MenuEntity.class);
	}

	public List<Menu> findByRoleId(Long roleId) throws SQLException {
		String sql = "select t.* from sys_menu t" + " inner join sys_role_menu t1 on t1.menu_id = t.id";
		Conditions condition = new Conditions();
		condition.and().is("t1.role_id", roleId);
		condition.asc("t.parent_id");
		condition.asc("t.priority");
		condition.asc("t.id");
		Query query = new Query(sql);
		query.appendSql(condition.toSql(query.getParams(), true));
		return this.jdbcHelper.find(query, Menu.class);
	}

	public List<Menu> findByManagerId(Long managerId) throws SQLException {
		String sql = "select distinct t.* from sys_menu t"
				+ " inner join sys_role_menu t1 on t1.menu_id = t.id"
				+ " inner join sys_manager_role t2 on t2.role_id = t1.role_id"
				+ " where t2.manager_id = ? order by t.parent_id asc,t.priority asc,t.id asc";
		return this.find(sql, managerId);
	}

	public List<Menu> findByManagerIdAndPermission(Long managerId, String permission) throws SQLException {
		String sql = "select distinct t.* from sys_menu t"
				+ " inner join sys_role_menu t1 on t1.menu_id = t.id"
				+ " inner join sys_manager_role t2 on t2.role_id = t1.role_id"
				+ " where t2.manager_id = ? and t.permission = ? order by t.parent_id asc,t.priority asc,t.id asc";
		return this.find(sql, managerId, permission);
	}
	
	public Long countByManagerIdAndPermission(Long managerId, String permission) throws SQLException {
		String sql = "select COUNT(1) from sys_menu t"
				+ " INNER JOIN sys_role_menu t1 on t1.menu_id = t.id"
				+ " INNER JOIN sys_manager_role t2 on t2.role_id = t1.role_id"
				+ " WHERE t2.manager_id = ? and t.permission = ? ";
		Query query = new Query(sql);
		query.getParams().setObject(managerId);
		query.getParams().setObject(permission);
		return this.jdbcHelper.getFirstField(query, Long.class);
	}
	
	public Long countByManagerIdAndPermission(Long managerId, String[] permission) throws SQLException {
		String sql = "select COUNT(1) from sys_menu t"
				+ " INNER JOIN sys_role_menu t1 on t1.menu_id = t.id"
				+ " INNER JOIN sys_manager_role t2 on t2.role_id = t1.role_id";
		Query query = new Query(sql);
		Conditions condition = new Conditions();
		condition.and().is("t2.manager_id", managerId);
		condition.and().in("t.permission", permission);
		query.appendSql(condition.toSql(query.getParams(), true));
		return this.jdbcHelper.getFirstField(query, Long.class);
	}
}
